﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.IO.Packaging;
using System.Linq;
using System.Text;
using System.Xml;
using System.Xml.XPath;
using Ionic.Utils.Zip;

namespace Common.OpenXml
{
	/// <summary>
	/// 
	/// </summary>
	public class ExcelReader
	{
        /// <summary>
        /// 
        /// </summary>
        /// <param name="excelFilePath"></param>
        /// <returns></returns>
		public static List<string> GetWorksheetNames(string excelFilePath)
		{
		    List<string> sheetNames=new List<string>();
            Package pkg = Package.Open(excelFilePath);
            Uri appUri = new Uri("/docProps/app.xml", UriKind.Relative);
            PackagePart part = pkg.GetPart(appUri);
            StreamReader reader = new StreamReader(part.GetStream());
            string xml = reader.ReadToEnd();
            reader.Close();
            XmlDocument xDoc= new XmlDocument();
            xDoc.LoadXml(xml);
            XmlNamespaceManager namespaceManager = new XmlNamespaceManager(xDoc.NameTable);
            namespaceManager.AddNamespace(Prefixes.VTType, Namespaces.DocPropVTType);
            namespaceManager.AddNamespace("default","http://schemas.openxmlformats.org/officeDocument/2006/extended-properties");

            XmlNodeList sheetNodes = xDoc.DocumentElement.SelectNodes("default:TitlesOfParts/vt:vector/vt:lpstr", namespaceManager);
            if(sheetNodes !=null && sheetNodes.Count>0)
            {
                foreach(XmlNode sheetNode in sheetNodes)
                {
                    sheetNames.Add(sheetNode.InnerText);
                }
            }
            pkg.Close();
            return sheetNames;
		}

        /// <summary>
        /// 
        /// </summary>
        /// <param name="excelFilePath"></param>
        /// <returns></returns>
        public static DataSet ReadData(string excelFilePath)
        {
            DataSet ds=new DataSet(Path.GetFileNameWithoutExtension(excelFilePath));
            List<string> sheetNames = ExcelReader.GetWorksheetNames(excelFilePath);
            if(sheetNames !=null && sheetNames.Count>0)
            {
                foreach(string sheetName in sheetNames)
                {
                    DataTable dt = ExcelReader.ReadTable(excelFilePath, sheetName);
                    ds.Tables.Add(dt);
                }
            }
            return ds;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="excelFilePath"></param>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public static DataTable ReadTable(string excelFilePath, string sheetName)
        {
            DataTable dt = new DataTable(sheetName);
            string sheetUrl = ExcelReader.GetWorksheetUrl(excelFilePath, sheetName);
            Uri sheetUri = new Uri(sheetUrl, UriKind.Relative);
            FileStream fs = File.OpenRead(excelFilePath);
            byte[] contents=new byte[fs.Length];
            fs.Read(contents, 0, (int) fs.Length);
            fs.Close();
            using (PackageHelper pkg = new PackageHelper(contents))
            {
                Dictionary<int,string> colIndexes=new Dictionary<int, string>();
                string xml = pkg.GetReadOnlyPart(sheetUri).CreateNavigator().OuterXml;
                SharedStrings sharedStrings=new SharedStrings(pkg);
                XmlDocument xdoc=new XmlDocument();
                xdoc.LoadXml(xml);
                XmlNamespaceManager namespaceManager = new XmlNamespaceManager(xdoc.NameTable);
                namespaceManager.AddNamespace(Prefixes.Relationships, Namespaces.Relationships);
                namespaceManager.AddNamespace("default", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");

                XmlNodeList rowNodes = xdoc.DocumentElement.SelectNodes("//default:sheetData/default:row", namespaceManager);
                XmlNode topRowNode = rowNodes[0];
                XmlNodeList colNodes = topRowNode.SelectNodes("default:c/default:v", namespaceManager);
                foreach (XmlNode colNode in colNodes)
                {
                    XmlNode typeNode = colNode.ParentNode;
                    string cellRange = typeNode.Attributes.GetNamedItem("r").Value;
                    int colIndex = ExcelRangeHelper.GetColumnIndex(cellRange);
                    if(typeNode.Attributes.GetNamedItem("t")!=null && typeNode.Attributes.GetNamedItem("t").Value=="s")
                    {
                        string colName = sharedStrings.GetString(int.Parse(colNode.InnerText));
                        colName = colName.Replace(" ", "_");
                        colName = colName.Replace(".", "_");
                        colName = colName.Replace("/", "_");
                        colName = colName.Replace("\\", "_");
                        colName = colName.Replace("(", "_");
                        colName = colName.Replace(")", "_");
                        colName = colName.Replace("{", "_");
                        colName = colName.Replace("}", "_");
                        colName = colName.Replace("[", "_");
                        colName = colName.Replace("]", "_");
                        colName = colName.Replace(",", "_");
                        colName = colName.Replace(":", "_");
                        colName = colName.Replace(";", "_");
                        while (colName.EndsWith("_"))
                            colName = colName.Substring(0, colName.Length - 1);
                        while (colName.StartsWith("_"))
                            colName = colName.Substring(1);
                        while (colName.IndexOf("__") > 0)
                            colName = colName.Replace("__", "_");
                        if(dt.Columns.Contains(colName))
                            continue;
                        colIndexes.Add(colIndex,colName);
                        dt.Columns.Add(colName, typeof (string));
                    }
                    else
                    {
                        if(dt.Columns.Contains(colNode.InnerText))
                            continue;
                        colIndexes.Add(colIndex, colNode.InnerText);
                        dt.Columns.Add(colNode.InnerText, typeof(string));
                    }
                }
                for(int i=1;i<rowNodes.Count;i++)
                {
                    DataRow dr = dt.NewRow();
                    XmlNodeList cellNodes = rowNodes[i].SelectNodes("default:c/default:v", namespaceManager);
                    foreach (XmlNode cellNode in cellNodes)
                    {
                        XmlNode typeNode = cellNode.ParentNode;
                        string cellRange = typeNode.Attributes.GetNamedItem("r").Value;
                        int colIndex = ExcelRangeHelper.GetColumnIndex(cellRange);
                        if(!colIndexes.ContainsKey(colIndex))
                            continue;
                        string colName = colIndexes[colIndex];
                        
                        if (typeNode.Attributes.GetNamedItem("t") != null && typeNode.Attributes.GetNamedItem("t").Value == "s")
                        {
                            string cellValue = sharedStrings.GetString(int.Parse(cellNode.InnerText));
                            dr[colName] = cellValue;
                        }
                        else
                        {
                            dr[colName] = cellNode.InnerText;
                        }
                    }
                    dt.Rows.Add(dr);
                }
            }
            return dt;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="excelFilePath"></param>
        /// <param name="sheetName"></param>
        /// <returns></returns>
	    public static List<string> ReadColumnHeaders(string excelFilePath, string sheetName)
	    {
	        List<string> colHeaders=new List<string>();
            string sheetUrl = ExcelReader.GetWorksheetUrl(excelFilePath, sheetName);
            Uri sheetUri = new Uri(sheetUrl, UriKind.Relative);
            FileStream fs = File.OpenRead(excelFilePath);
            byte[] contents=new byte[fs.Length];
            fs.Read(contents, 0, (int) fs.Length);
            fs.Close();
            using (PackageHelper pkg = new PackageHelper(contents))
            {
                string xml = pkg.GetReadOnlyPart(sheetUri).CreateNavigator().OuterXml;
                SharedStrings sharedStrings = new SharedStrings(pkg);
                XmlDocument xdoc = new XmlDocument();
                xdoc.LoadXml(xml);
                XmlNamespaceManager namespaceManager=new XmlNamespaceManager(xdoc.NameTable);
                namespaceManager.AddNamespace(Prefixes.Relationships, Namespaces.Relationships);
                namespaceManager.AddNamespace("default", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");

                XmlNodeList rowNodes = xdoc.DocumentElement.SelectNodes("//default:sheetData/default:row", namespaceManager);
                XmlNode topRowNode = rowNodes[0];
                XmlNodeList colNodes = topRowNode.SelectNodes("default:c/default:v", namespaceManager);
                foreach (XmlNode colNode in colNodes)
                {
                    XmlNode typeNode = colNode.ParentNode;
                    if (typeNode.Attributes.GetNamedItem("t") != null && typeNode.Attributes.GetNamedItem("t").Value == "s")
                    {
                        string colName = sharedStrings.GetString(int.Parse(colNode.InnerText));
                        if (colHeaders.Contains(colName))
                            continue;
                        colHeaders.Add(colName);
                    }
                    else
                    {
                        if (colHeaders.Contains(colNode.InnerText))
                            continue;
                        colHeaders.Add(colNode.InnerText);
                    }
                }
            }
            return colHeaders;
	    }

        private static string GetWorksheetUrl(string excelFilePath, string worksheetName)
        {
            string sheetUri = string.Empty;
            FileStream fs = File.OpenRead(excelFilePath);
            byte[] contents=new byte[fs.Length];
            fs.Read(contents, 0, (int) fs.Length);
            fs.Close();
            using (PackageHelper pkg = new PackageHelper(contents))
            {
                Uri workbookUri=new Uri("/xl/workbook.xml",UriKind.Relative);
                string wkXml = pkg.GetReadOnlyPart(workbookUri).CreateNavigator().OuterXml;
                XmlDocument wkDoc=new XmlDocument();
                wkDoc.LoadXml(wkXml);
                XmlNamespaceManager nmMgr = new XmlNamespaceManager(wkDoc.NameTable);
                nmMgr.AddNamespace("default", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
                nmMgr.AddNamespace("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
                XmlNodeList sheetNodes = wkDoc.DocumentElement.SelectNodes("default:sheets/default:sheet", nmMgr);
                string rid = "rId1";
                foreach(XmlNode sheetNode in sheetNodes)
                {
                    if(sheetNode.Attributes.GetNamedItem("name").Value == worksheetName)
                    {
                        rid = sheetNode.Attributes.GetNamedItem("r:id").Value;
                    }
                }
                Uri workbookRelUri = new Uri("/xl/_rels/workbook.xml.rels", UriKind.Relative);
                string xml = pkg.GetReadOnlyPart(workbookRelUri).CreateNavigator().OuterXml;
                XmlDocument xDoc = new XmlDocument();
                xDoc.LoadXml(xml);
                XmlNamespaceManager namespaceManager=new XmlNamespaceManager(xDoc.NameTable);
                namespaceManager.AddNamespace("default", "http://schemas.openxmlformats.org/package/2006/relationships");
                XmlNode sheetRelNode = xDoc.DocumentElement.SelectSingleNode(string.Format("default:Relationship[@Id=\"{0}\"]", rid), namespaceManager);
                string target = sheetRelNode.Attributes.GetNamedItem("Target").Value;
                sheetUri = string.Format("/xl/{0}",target);
            }
            return sheetUri;
        }
	}
}
